<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <meta name="keywords" content="Hexo Theme Redefine">
    <meta name="description" content="Hexo Theme Redefine">
    <meta name="author" content="Sakura">
    
    <title>
        
            几个简单好用的SQL语法 |
        
        Sakura Blog
    </title>
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    <link rel="preconnect" href="https://evan.beee.top" crossorigin>
    
<link rel="stylesheet" href="/sakura-blog/css/style.css">

    <link rel="shortcut icon" href="/sakura-blog/images/bitbug_favicon.ico">
    
<link rel="stylesheet" href="/sakura-blog/css/fontawesome.min.css">

    
<link rel="stylesheet" href="/sakura-blog/css/brands.min.css">

    
<link rel="stylesheet" href="/sakura-blog/css/solid.min.css">

    
<link rel="stylesheet" href="/sakura-blog/css/regular.min.css">

    
    
    
    
<link rel="stylesheet" href="/sakura-blog/css/css2.css">

    <script id="hexo-configurations">
    let REDEFINE = window.REDEFINE || {};
    REDEFINE.hexo_config = {"hostname":"xindong_a.gitee.io","root":"/sakura-blog/","language":"zh-CN"};
    REDEFINE.theme_config = {"toc":{"enable":true,"number":false,"expand_all":true,"init_open":true},"style":{"primary_color":"#005080","avatar":"/images/avatar.jpg","favicon":"/images/bitbug_favicon.ico","article_img_align":"center","right_side_width":"210px","content_max_width":"1000px","nav_color":{"left":"#f78736","right":"#367df7","transparency":35},"hover":{"shadow":true,"scale":false},"first_screen":{"enable":true,"background_image":{"light":"https://evan.beee.top/img/wallhaven-wqery6-light.webp","dark":"https://evan.beee.top/img/wallhaven-wqery6-dark.webp"},"title_color":{"light":"#fff","dark":"#d1d1b6"},"description":"宁静致远"},"scroll":{"progress_bar":{"enable":true},"percent":{"enable":false}}},"local_search":{"enable":false,"preload":true},"code_block":{"copy":true,"style":"mac"},"pjax":{"enable":true},"lazyload":{"enable":true},"version":"0.4.6","friend_links":{"columns":2}};
    REDEFINE.language_ago = {"second":"%s 秒前","minute":"%s 分钟前","hour":"%s 小时前","day":"%s 天前","week":"%s 周前","month":"%s 个月前","year":"%s 年前"};
  </script>
<meta name="generator" content="Hexo 6.3.0"></head>


<body>
<div class="progress-bar-container">
    
        <span class="scroll-progress-bar"></span>
    

    
        <span class="pjax-progress-bar"></span>
        <span class="pjax-progress-icon">
            <i class="fa-solid fa-circle-notch fa-spin"></i>
        </span>
    
</div>


<main class="page-container">

    

    <div class="page-main-content">

        <div class="page-main-content-top">
            <header class="header-wrapper">
    
    <div class="header-content">
        <div class="left">
            
            <a class="logo-title" href="/">
                Sakura Blog
            </a>
        </div>

        <div class="right">
            <!-- PC -->
            <div class="pc">
                <ul class="menu-list">
                    
                        <li class="menu-item">
                            <!-- Menu -->
                            <a class="" 
                                href="/sakura-blog/" >
                                
                                    
                                        <i class="fa-regular fa-house"></i>
                                    
                                    首页
                                
                            </a>
                            <!-- Submenu -->
                            
                        </li>
                    
                        <li class="menu-item">
                            <!-- Menu -->
                            <a class="" 
                                href="/sakura-blog/archives" >
                                
                                    
                                        <i class="fa-regular fa-archive"></i>
                                    
                                    归档
                                
                            </a>
                            <!-- Submenu -->
                            
                        </li>
                    
                        <li class="menu-item">
                            <!-- Menu -->
                            <a class="" 
                                target="_blank" rel="noopener" href="https://status.evanluo.top/" >
                                
                                    
                                        <i class="fa-regular fa-chart-bar"></i>
                                    
                                    状态
                                
                            </a>
                            <!-- Submenu -->
                            
                        </li>
                    
                        <li class="menu-item">
                            <!-- Menu -->
                            <a class="has-dropdown" 
                                href="javascript:void(0);">
                                
                                    
                                        <i class="fa-regular fa-user"></i>
                                    
                                    关于&nbsp;<i class="fa-solid fa-chevron-down"></i>
                                
                            </a>
                            <!-- Submenu -->
                            
                                <ul class="sub-menu">
                                  
                                    <li>
                                      <a href="/sakura-blog/about">ME
                                      </a>
                                    </li>
                                  
                                    <li>
                                      <a target="_blank" rel="noopener" href="https://github.com/EvanNotFound/hexo-theme-redefine">GITHUB
                                      </a>
                                    </li>
                                  
                                    <li>
                                      <a target="_blank" rel="noopener" href="https://www.evanluo.top">BLOG
                                      </a>
                                    </li>
                                  
                                    <li>
                                      <a href="/sakura-blog/friends">FRIENDS
                                      </a>
                                    </li>
                                  
                                </ul>
                            
                        </li>
                    
                    
                </ul>
            </div>
            <!-- Mobile -->
            <div class="mobile">
                
                <div class="icon-item menu-bar">
                    <div class="menu-bar-middle"></div>
                </div>
            </div>
        </div>
    </div>

    <!-- Mobile drawer -->
    <div class="header-drawer">
        <ul class="drawer-menu-list">
            
                <li class="drawer-menu-item flex-center">
                    <a class="" 
                       href="/sakura-blog/" >
                         
                            
                                <i class="fa-regular fa-house"></i>
                            
                            首页
                        
                    </a>
                </li>
                <!-- Submenu -->
                
            
                <li class="drawer-menu-item flex-center">
                    <a class="" 
                       href="/sakura-blog/archives" >
                         
                            
                                <i class="fa-regular fa-archive"></i>
                            
                            归档
                        
                    </a>
                </li>
                <!-- Submenu -->
                
            
                <li class="drawer-menu-item flex-center">
                    <a class="" 
                       target="_blank" rel="noopener" href="https://status.evanluo.top/" >
                         
                            
                                <i class="fa-regular fa-chart-bar"></i>
                            
                            状态
                        
                    </a>
                </li>
                <!-- Submenu -->
                
            
                <li class="drawer-menu-item flex-center">
                    <a class="has-dropdown" 
                       href="javascript:void(0);">
                        
                            
                                <i class="fa-regular fa-user"></i>
                            
                            关于&nbsp;<i class="fa-solid fa-chevron-down"></i>
                        
                    </a>
                </li>
                <!-- Submenu -->
                          
                    
                        <li class="dropdown-item flex-center">
                            <a class="dropdown-item" href="/sakura-blog/about">ME</a>
                        </li>
                    
                        <li class="dropdown-item flex-center">
                            <a class="dropdown-item" target="_blank" rel="noopener" href="https://github.com/EvanNotFound/hexo-theme-redefine">GITHUB</a>
                        </li>
                    
                        <li class="dropdown-item flex-center">
                            <a class="dropdown-item" target="_blank" rel="noopener" href="https://www.evanluo.top">BLOG</a>
                        </li>
                    
                        <li class="dropdown-item flex-center">
                            <a class="dropdown-item" href="/sakura-blog/friends">FRIENDS</a>
                        </li>
                    
                
            
        </ul>
    </div>

    <div class="window-mask"></div>

</header>


        </div>

        <div class="page-main-content-middle">

            <div class="main-content">

                
                    <div class="fade-in-down-animation">
    <div class="post-page-container">
        <div class="article-content-container">
            <div class="article-title">
                <span class="title-hover-animation"><h1 style="font-size:2rem; font-weight: bold; margin: 10px 0;">几个简单好用的SQL语法</h1></span>
            </div>

            
                <div class="article-header">
                    <div class="avatar">
                        <img src="/sakura-blog/images/avatar.jpg">
                    </div>
                    <div class="info">
                        <div class="author">
                            <span class="name">Sakura</span>
                            
                                <span class="author-label">lol</span>
                            
                        </div>
                        <div class="meta-info">
                            <div class="article-meta-info">
    <span class="article-date article-meta-item">
        <i class="fa-regular fa-pen-fancy"></i>&nbsp;
        <span class="pc">2023-01-09 09:26:08</span>
        <span class="mobile">2023-01-09 09:26</span>
    </span>
    
    
        <span class="article-tags article-meta-item">
            <i class="fa-regular fa-tags"></i>&nbsp;
            <ul>
                
                    <li>
                        <a href="/sakura-blog/tags/sql/">sql</a>&nbsp;
                    </li>
                
            </ul>
        </span>
    

    
    
    
    
        <span class="article-pv article-meta-item">
            <i class="fa-regular fa-eye"></i>&nbsp;<span id="busuanzi_value_page_pv"></span>
        </span>
    
</div>

                        </div>
                    </div>
                </div>
            

            <div class="article-content markdown-body">
                <h3 id="前言"><a href="#前言" class="headerlink" title="前言"></a><strong>前言</strong></h3><p>​		数据库和 SQL 语句几乎是所有程序员的必修课，但是据我观察下来，学校教的数据库和 SQL 都		比较基础和 “机械”，以至于很多同学对数据库的印象就是增删改查，自己写 SQL 的能力也很差。</p>
<p>​		工作一段时间后,随着业务的深入理解，接触到了更多的SQL语法，才意识到之前学的、用的SQL		还是太少了。</p>
<p>​		今天以MySQl的语法为例，分享给大家一些非常有用的SQL语句，并不复杂，希望能对大家有所帮		助！</p>
<hr>
<h3 id="1-group-concat"><a href="#1-group-concat" class="headerlink" title="1.group_concat"></a>1.group_concat</h3><p>​		平常工作中，使用 group by 进行分组的场景，是非常多的。</p>
<p>​		比如想统计用户表中，名称不同的用户的具体名称有哪些？</p>
<p>​		具体sql如下：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> name <span class="keyword">from</span> `<span class="keyword">user</span>`</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> name;</span><br></pre></td></tr></table></figure></div>

<p>​		但如果想把name相同的code拼接在一起，放到另外一列中该怎么办呢？</p>
<p>​		呢我们就可以使用到 group_concat 函数啦！</p>
<p>​		例如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> name,group_concat(code) <span class="keyword">from</span> `<span class="keyword">user</span>`</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> name;</span><br></pre></td></tr></table></figure></div>

<p>​		执行结果：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/01.png" class title="group_concat语法结果示例"></strong></p>
<p>​		使用<code>group_concat</code>函数，可以轻松的把分组后，name相同的数据拼接到一起，组成一个字符串，		用<code>逗号</code>分隔</p>
<h3 id="2-char-length"><a href="#2-char-length" class="headerlink" title="2.char_length"></a>2.char_length</h3><p>​		有时候我们需要获取字符的<code>长度</code>，然后根据字符的长度进行<code>排序</code>。</p>
<p>​		MYSQL给我们提供了一些有用的函数，比如：<code>char_length</code>。</p>
<p>​		通过该函数就能获取字符长度。</p>
<p>​		获取字符长度并且排序的sql如下：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> brand <span class="keyword">where</span> name <span class="keyword">like</span> <span class="string">&#x27;%苏三%&#x27;</span> </span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">char_length</span>(name) <span class="keyword">asc</span> limit <span class="number">5</span>;</span><br></pre></td></tr></table></figure></div>

<p>​		执行效果如图所示：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/02.png" class title="char_length语法结果示例"></strong></p>
<p>​		name字段使用关键字<code>模糊查询</code>之后，再使用<code>char_length</code>函数获取name字段的字符长度，然后		按长度<code>升序</code>。</p>
<h3 id="3-locate"><a href="#3-locate" class="headerlink" title="3.locate"></a>3.locate</h3><p>​		有时候我们在查找某个关键字，比如：<code>苏三</code>，需要明确知道它在某个字符串中的位置时，该怎么		办呢？</p>
<p>​		答：使用<code>locate</code>函数。</p>
<p>​		使用locate函数改造之后sql如下：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> brand <span class="keyword">where</span> name <span class="keyword">like</span> <span class="string">&#x27;%苏三%&#x27;</span> </span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">char_length</span>(name) <span class="keyword">asc</span>, locate(<span class="string">&#x27;苏三&#x27;</span>,name) <span class="keyword">asc</span> limit <span class="number">5</span>,<span class="number">5</span>;</span><br></pre></td></tr></table></figure></div>

<p>​		执行结果：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/03.png" class title="locate语法结果示例"></strong></p>
<p>​		先按长度排序，小的排在前面。如果长度相同，则按关键字从左到右进行排序，越靠左的越排在前		面。</p>
<p>​		除此之外，我们还可以使用：<code>instr</code>和<code>position</code>函数，它们的功能跟<code>locate</code>函数类似，在这里就		不一一介绍了。</p>
<h3 id="4-replace"><a href="#4-replace" class="headerlink" title="4.replace"></a>4.replace</h3><p>​		我们经常会有替换字符串中部分内容的需求，比如：将字符串中的字符A替换成B。</p>
<p>​		这种情况就能使用<code>replace</code>函数。</p>
<p>​		例如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">update</span> brand <span class="keyword">set</span> name<span class="operator">=</span>REPLACE(name,<span class="string">&#x27;A&#x27;</span>,<span class="string">&#x27;B&#x27;</span>) </span><br><span class="line"><span class="keyword">where</span> id<span class="operator">=</span><span class="number">1</span>;</span><br></pre></td></tr></table></figure></div>

<p>​		这样就能轻松实现字符替换功能。</p>
<p>​		也能用该函数去掉<code>前后空格</code>：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">update</span> brand <span class="keyword">set</span> name<span class="operator">=</span>REPLACE(name,<span class="string">&#x27; &#x27;</span>,<span class="string">&#x27;&#x27;</span>) <span class="keyword">where</span> name <span class="keyword">like</span> <span class="string">&#x27; %&#x27;</span>;</span><br><span class="line"><span class="keyword">update</span> brand <span class="keyword">set</span> name<span class="operator">=</span>REPLACE(name,<span class="string">&#x27; &#x27;</span>,<span class="string">&#x27;&#x27;</span>) <span class="keyword">where</span> name <span class="keyword">like</span> <span class="string">&#x27;% &#x27;</span>;</span><br></pre></td></tr></table></figure></div>

<p>​		使用该函数还能替换<code>json格式</code>的数据内容，真的非常有用。</p>
<h3 id="5-insert-into-…-select"><a href="#5-insert-into-…-select" class="headerlink" title="5.insert into … select"></a>5.insert into … select</h3><p>​		在工作中很多时候需要<code>插入数据</code>。</p>
<p>​		传统的插入数据的sql是这样的：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `brand`(`id`, `code`, `name`, `edit_date`) </span><br><span class="line"><span class="keyword">VALUES</span> (<span class="number">5</span>, <span class="string">&#x27;108&#x27;</span>, <span class="string">&#x27;迪迦&#x27;</span>, <span class="string">&#x27;2022-09-02 19:42:21&#x27;</span>);</span><br></pre></td></tr></table></figure></div>

<p>​		它主要是用于插入少量并且已经确定的数据。但如果有大批量的数据需要插入，特别是是需要插入		的数据来源于，另外一张表或者多张表的结果集中。</p>
<p>​		这种情况下，使用传统的插入数据的方式，就有点束手无策了。</p>
<p>​		这时候就能使用MYSQL提供的：<code>insert into ... select</code>语法。</p>
<p>​		例如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `brand`(`id`, `code`, `name`, `edit_date`) </span><br><span class="line"><span class="keyword">select</span> <span class="keyword">null</span>,code,name,now(<span class="number">3</span>) <span class="keyword">from</span> `<span class="keyword">order</span>` <span class="keyword">where</span> code <span class="keyword">in</span> (<span class="string">&#x27;004&#x27;</span>,<span class="string">&#x27;005&#x27;</span>);</span><br></pre></td></tr></table></figure></div>

<p>​		这样就能将order表中的部分数据，非常轻松插入到brand表中。</p>
<h3 id="6-insert-into-…-ignore"><a href="#6-insert-into-…-ignore" class="headerlink" title="6.insert into … ignore"></a>6.insert into … ignore</h3><p>​		不知道你有没有遇到过这样的场景：在插入1000个品牌之前，需要先根据name，判断一下是否存		在。如果存在，则不插入数据。如果不存在，才需要插入数据。</p>
<p>​		如果直接这样插入数据：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `brand`(`id`, `code`, `name`, `edit_date`) </span><br><span class="line"><span class="keyword">VALUES</span> (<span class="number">123</span>, <span class="string">&#x27;108&#x27;</span>, <span class="string">&#x27;苏三&#x27;</span>, now(<span class="number">3</span>));</span><br></pre></td></tr></table></figure></div>

<p>​		肯定不行，因为brand表的name字段创建了唯一索引，同时该表中已经有一条name等于苏三的数		据了。</p>
<p>​		执行之后直接报错了：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/04.png" class title="结果示例"></strong></p>
<p>​		这就需要在插入之前加一下判断。</p>
<p>​		当然很多人通过在sql语句后面拼接<code>not exists</code>语句，也能达到防止出现重复数据的目的，比如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `brand`(`id`, `code`, `name`, `edit_date`) </span><br><span class="line"><span class="keyword">select</span> <span class="keyword">null</span>,<span class="string">&#x27;108&#x27;</span>, <span class="string">&#x27;苏三&#x27;</span>,now(<span class="number">3</span>) </span><br><span class="line"><span class="keyword">from</span> dual <span class="keyword">where</span>  <span class="keyword">not</span> <span class="keyword">exists</span> (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> `brand` <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;苏三&#x27;</span>);</span><br></pre></td></tr></table></figure></div>

<p>​		这条sql确实能够满足要求，但是总觉得有些麻烦。那么，有没有更简单的做法呢？</p>
<p>​		答：可以使用<code>insert into ... ignore</code>语法。</p>
<p>​		例如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> ignore <span class="keyword">INTO</span> `brand`(`id`, `code`, `name`, `edit_date`) </span><br><span class="line"><span class="keyword">VALUES</span> (<span class="number">123</span>, <span class="string">&#x27;108&#x27;</span>, <span class="string">&#x27;苏三&#x27;</span>, now(<span class="number">3</span>));</span><br></pre></td></tr></table></figure></div>

<p>​		这样改造之后，如果brand表中没有name为苏三的数据，则可以直接插入成功。</p>
<p>​		但如果brand表中已经存在name为苏三的数据了，则该sql语句也能正常执行，并不会报错。因为		它会忽略异常，返回的执行结果影响行数为0，它不会重复插入数据。</p>
<h3 id="7-select-…-for-update"><a href="#7-select-…-for-update" class="headerlink" title="7.select … for update"></a>7.select … for update</h3><p>​		MYSQL数据库自带了<code>悲观锁</code>，它是一种排它锁，根据锁的粒度从大到小分为：<code>表锁</code>、<code>间隙锁</code>和<code>行锁</code>。</p>
<p>​		在我们的实际业务场景中，有些情况并发量不太高，为了保证数据的正确性，使用悲观锁也可以。</p>
<p>​		比如：用户扣减积分，用户的操作并不集中。但也要考虑系统自动赠送积分的并发情况，所以有必		要加悲观锁限制一下，防止出现积分加错的情况发生。</p>
<p>​		这时候就可以使用MYSQL中的<code>select ... for update</code>语法了。</p>
<p>​		例如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">begin</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> `<span class="keyword">user</span>` <span class="keyword">where</span> id<span class="operator">=</span><span class="number">1</span> </span><br><span class="line"><span class="keyword">for</span> <span class="keyword">update</span>;</span><br><span class="line"></span><br><span class="line"><span class="operator">/</span><span class="operator">/</span>业务逻辑处理</span><br><span class="line"></span><br><span class="line"><span class="keyword">update</span> `<span class="keyword">user</span>` <span class="keyword">set</span> score<span class="operator">=</span>score<span class="number">-1</span> <span class="keyword">where</span> id<span class="operator">=</span><span class="number">1</span>;</span><br><span class="line"><span class="keyword">commit</span>;</span><br></pre></td></tr></table></figure></div>

<p>​		这样在一个事务中使用<code>for update</code>锁住一行记录，其他事务就不能在该事务提交之前，去更新那		一行的数据。</p>
<p>​		需要注意的是for update前的id条件，必须是表的<code>主键</code>或者<code>唯一索引</code>，不然行锁可能会失效，有可		能变成<code>表锁</code>。</p>
<h3 id="8-on-duplicate-key-update"><a href="#8-on-duplicate-key-update" class="headerlink" title="8.on duplicate key update"></a>8.on duplicate key update</h3><p>​		通常情况下，我们在插入数据之前，一般会先查询一下，该数据是否存在。如果不存在，则插入数		据。如果已存在，则不插入数据，而直接返回结果。</p>
<p>​		在没啥并发量的场景中，这种做法是没有什么问题的。但如果插入数据的请求，有一定的并发量，		这种做法就可能会产生重复的数据。</p>
<p>​		当然防止重复数据的做法很多，比如：<code>加唯一索引</code>、<code>加分布式锁</code>等。</p>
<p>​		但这些方案，都没法做到让第二次请求也更新数据，它们一般会判断已经存在就直接返回了。</p>
<p>​		这种情况可以使用<code>on duplicate key update</code>语法。</p>
<p>​		该语法会在插入数据之前判断，如果主键或唯一索引不存在，则插入数据。如果主键或唯一索引存		在，则执行更新操作。</p>
<p>​		具体需要更新的字段可以指定，例如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span>  <span class="keyword">INTO</span> `brand`(`id`, `code`, `name`, `edit_date`) </span><br><span class="line"><span class="keyword">VALUES</span> (<span class="number">123</span>, <span class="string">&#x27;108&#x27;</span>, <span class="string">&#x27;奥特曼&#x27;</span>, now(<span class="number">3</span>))</span><br><span class="line"><span class="keyword">on</span> duplicate key <span class="keyword">update</span> name<span class="operator">=</span><span class="string">&#x27;奥特曼&#x27;</span>,edit_date<span class="operator">=</span>now(<span class="number">3</span>);</span><br></pre></td></tr></table></figure></div>

<p>​		这样一条语句就能轻松搞定需求，既不会产生重复数据，也能更新最新的数据。</p>
<p>​		但需要注意的是，在高并发的场景下使用<code>on duplicate key update</code>语法，可能会存在<code>死锁</code>的问		题，所以要根据实际情况酌情使用。</p>
<h3 id="9-show-create-table"><a href="#9-show-create-table" class="headerlink" title="9.show create table"></a>9.show create table</h3><p>​		有时候，我们想快速查看某张表的字段情况，通常会使用<code>desc</code>命令，比如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">desc</span> `<span class="keyword">order</span>`;</span><br></pre></td></tr></table></figure></div>

<p>​		结果如图所示：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/05.png" class title="结果示例"></strong></p>
<p>​		确实能够看到order表中的字段名称、字段类型、字段长度、是否允许为空，是否主键、默认值等		信息。</p>
<p>​		但看不到该表的索引信息，如果想看创建了哪些索引，该怎么办呢？</p>
<p>​		答：使用<code>show index</code>命令。</p>
<p>​		比如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> index <span class="keyword">from</span> `<span class="keyword">order</span>`;</span><br></pre></td></tr></table></figure></div>

<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/06.png" class title="结果示例"></strong></p>
<p>​		但查看字段和索引数据呈现方式，总觉得有点怪怪的，有没有一种更直观的方式？</p>
<p>​		答：这就需要使用<code>show create table</code>命令了。</p>
<p>​		例如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">table</span> `<span class="keyword">order</span>`;</span><br></pre></td></tr></table></figure></div>

<p>​		执行结果如图所示：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/07.png" class title="结果示例"></strong></p>
<p>​		其中<code>Table</code>表示<code>表名</code>，<code>Create Table</code>就是我们需要看的<code>建表信息</code>，将数据展开：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/08.png" class title="结果示例"></strong></p>
<p>​		我们能够看到非常完整的建表语句，表名、字段名、字段类型、字段长度、字符集、主键、索引、		执行引擎等都能看到。</p>
<p>​		非常直接明了。</p>
<h3 id="10-create-table-…-select"><a href="#10-create-table-…-select" class="headerlink" title="10.create table … select"></a>10.create table … select</h3><p>​		有时候，我们需要快速备份表。</p>
<p>​		通常情况下，可以分两步走：</p>
<ol>
<li>创建一张临时表</li>
<li>将数据插入临时表</li>
</ol>
<p>​		创建临时表可以使用命令：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> order_2022121819 <span class="keyword">like</span> `<span class="keyword">order</span>`;</span><br></pre></td></tr></table></figure></div>

<p>​		创建成功之后，就会生成一张名称叫：order_2022121819，表结构跟order一模一样的<code>新表</code>，只		是该表的<code>数据为空</code>而已。</p>
<p>​		接下来使用命令：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> order_2022121819 <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> `<span class="keyword">order</span>`;</span><br></pre></td></tr></table></figure></div>

<p>​		执行之后就会将order表的数据插入到order_2022121819表中，也就是实现数据备份的功能。</p>
<p>​		但有没有命令，一个命令就能实现上面这两步的功能呢？</p>
<p>​		答：用<code>create table ... select</code>命令。</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> order_2022121820 </span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> `<span class="keyword">order</span>`;</span><br></pre></td></tr></table></figure></div>

<p>​		执行完之后，就会将order_2022121820表创建好，并且将order表中的数据自动插入到新创建的		order_2022121820中。</p>
<p>​		一个命令就能轻松搞定<code>表备份</code>。</p>
<h3 id="11-explain"><a href="#11-explain" class="headerlink" title="11.explain"></a>11.explain</h3><p>​		很多时候，我们优化一条sql语句的性能，需要查看<code>索引</code>执行情况。</p>
<p>​		答：可以使用<code>explain</code>命令，查看mysql的<code>执行计划</code>，它会显示<code>索引的使用情况</code>。</p>
<p>​		例如：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">explain <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> `<span class="keyword">order</span>` <span class="keyword">where</span> code<span class="operator">=</span><span class="string">&#x27;002&#x27;</span>;</span><br></pre></td></tr></table></figure></div>

<p>​		结果：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/09.png" class title="结果示例"></strong></p>
<p>​		通过这几列可以判断索引使用情况，执行计划包含列的含义如下图所示：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/10.png" class title="结果示例"></strong></p>
<p>​		说实话，sql语句没有走索引，排除没有建索引之外，最大的可能性是索引失效了。</p>
<p>​		下面说说索引失效的常见原因：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/11.png" class title="结果示例"></strong></p>
<p>​		如果不是上面的这些原因，则需要再进一步排查一下其他原因。</p>
<h3 id="12-show-processlist"><a href="#12-show-processlist" class="headerlink" title="12.show processlist"></a>12.show processlist</h3><p>​		有些时候我们线上sql或者数据库出现了问题。比如出现了数据库连接过多问题，或者发现有一条		sql语句的执行时间特别长。</p>
<p>​		这时候该怎么办呢？</p>
<p>​		答：我们可以使用<code>show processlist</code>命令查看<code>当前线程执行情况</code>。</p>
<p>​		如图所示：</p>
<p>​		<strong><img lazyload src="/images/loading.svg" data-src="/sakura-blog/2023/01/09/%E5%87%A0%E4%B8%AA%E7%AE%80%E5%8D%95%E5%A5%BD%E7%94%A8%E7%9A%84SQL%E8%AF%AD%E6%B3%95/12.png" class title="结果示例"></strong></p>
<p>​		从执行结果中，我们可以查看当前的连接状态，帮助识别出有问题的查询语句。</p>
<ul>
<li><p>id 线程id</p>
</li>
<li><p>User 执行sql的账号</p>
</li>
<li><p>Host 执行sql的数据库的ip和端号</p>
</li>
<li><p>db 数据库名称</p>
</li>
<li><p>Command 执行命令，包括：Daemon、Query、Sleep等。</p>
</li>
<li><p>Time 执行sql所消耗的时间</p>
</li>
<li><p>State 执行状态</p>
</li>
<li><p>info 执行信息，里面可能包含sql信息。</p>
<p>如果发现了异常的sql语句，可以直接kill掉，确保数据库不会出现严重的问题。</p>
<h3 id="13-mysqldump"><a href="#13-mysqldump" class="headerlink" title="13.mysqldump"></a>13.mysqldump</h3><p>有时候我们需要导出 MYSQL 表中的数据。</p>
<p>这种情况就可以使用<code>mysqldump</code>工具，该工具会将数据查出来，转换成insert语句，写入到某个文件中，相当于<code>数据备份</code>。</p>
<p>我们获取到该文件，然后执行相应的insert语句，就能创建相关的表，并且写入数据了，这就相当于<code>数据还原</code>。</p>
<p>mysqldump命令的语法为：<code>mysqldump -h主机名 -P端口 -u用户名 -p密码 参数1,参数2.... &gt; 文件名称.sql</code></p>
<p>备份远程数据库中的数据库：</p>
<div class="highlight-container" data-rel="Sql"><figure class="iseeu highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump <span class="operator">-</span>h <span class="number">192.22</span><span class="number">.25</span><span class="number">.226</span> <span class="operator">-</span>u root <span class="operator">-</span>p123456 dbname <span class="operator">&gt;</span> backup.sql</span><br></pre></td></tr></table></figure></div></li>
</ul>
<p>​		</p>

            </div>

            
                <div class="post-copyright-info">
                    <div class="article-copyright-info-container">
    <ul>
        <li>本文标题：几个简单好用的SQL语法</li>
        <li>本文作者：Sakura</li>
        <li>创建时间：2023-01-09 09:26:08</li>
        <li>
            本文链接：https://xindong_a.gitee.io/sakura-blog/2023/01/09/几个简单好用的SQL语法/
        </li>
        <li>
            版权声明：本博客所有文章除特别声明外，均采用 <a class="license" target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh">BY-NC-SA</a> 许可协议。转载请注明出处！
        </li>
    </ul>
</div>

                </div>
            

            
                <ul class="post-tags-box">
                    
                        <li class="tag-item">
                            <a href="/sakura-blog/tags/sql/">#sql</a>&nbsp;
                        </li>
                    
                </ul>
            

            
                <div class="article-nav">
                    
                        <div class="article-prev">
                            <a class="prev"
                            rel="prev"
                            href="/sakura-blog/2023/01/09/%E5%A6%82%E4%BD%95%E5%86%99%E5%87%BA%E4%B8%80%E4%B8%AA%E4%BC%98%E9%9B%85%E7%9A%84%E6%8E%A5%E5%8F%A3/"
                            >
                                <span class="left arrow-icon flex-center">
                                    <i class="fa-solid fa-chevron-left"></i>
                                </span>
                                <span class="title flex-center">
                                    <span class="post-nav-title-item">如何设计一个优雅的接口</span>
                                    <span class="post-nav-item">上一篇</span>
                                </span>
                            </a>
                        </div>
                    
                    
                        <div class="article-next">
                            <a class="next"
                            rel="next"
                            href="/sakura-blog/2023/01/04/%E5%9F%BA%E4%BA%8EGitee-Hexo-%E6%90%AD%E5%BB%BA%E7%9A%84%E5%8D%9A%E5%AE%A2/"
                            >
                                <span class="title flex-center">
                                    <span class="post-nav-title-item">基于 Gitee+Hexo 快速搭建自己的低成本博客平台</span>
                                    <span class="post-nav-item">下一篇</span>
                                </span>
                                <span class="right arrow-icon flex-center">
                                    <i class="fa-solid fa-chevron-right"></i>
                                </span>
                            </a>
                        </div>
                    
                </div>
            

            
        </div>

        
            <div class="toc-content-container">
                <div class="post-toc-wrap">
    <div class="post-toc">
        <div style="font-size: 1.3rem;margin-top: 0; margin-bottom: 0.8rem; transition-duration: 0.1s;"><i class="fa-solid fa-list"></i> <strong>目录</strong></div>
        <ol class="nav"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%89%8D%E8%A8%80"><span class="nav-text">前言</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#1-group-concat"><span class="nav-text">1.group_concat</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#2-char-length"><span class="nav-text">2.char_length</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#3-locate"><span class="nav-text">3.locate</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#4-replace"><span class="nav-text">4.replace</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#5-insert-into-%E2%80%A6-select"><span class="nav-text">5.insert into … select</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#6-insert-into-%E2%80%A6-ignore"><span class="nav-text">6.insert into … ignore</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#7-select-%E2%80%A6-for-update"><span class="nav-text">7.select … for update</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#8-on-duplicate-key-update"><span class="nav-text">8.on duplicate key update</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#9-show-create-table"><span class="nav-text">9.show create table</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#10-create-table-%E2%80%A6-select"><span class="nav-text">10.create table … select</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#11-explain"><span class="nav-text">11.explain</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#12-show-processlist"><span class="nav-text">12.show processlist</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#13-mysqldump"><span class="nav-text">13.mysqldump</span></a></li></ol>
    </div>
</div>
            </div>
        
    </div>
</div>


                

            </div>



        </div>

        <div class="page-main-content-bottom">
            <footer class="footer">
    <div class="info-container">
        <div class="copyright-info info-item">
            &copy;
            
              <span>2023</span>
              -
            
            2023&nbsp;<i class="fa-solid fa-heart icon-animate"></i>&nbsp;<a href="/">Sakura. 版权所有.</a>
        </div>
        
            <script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
            <div class="website-count info-item">
                
                    <span id="busuanzi_container_site_uv">
                        访问人数&nbsp;<span id="busuanzi_value_site_uv"></span>&ensp;
                    </span>
                
                
                    <span id="busuanzi_container_site_pv">
                        总访问量&nbsp;<span id="busuanzi_value_site_pv"></span>
                    </span>
                
            </div>
        
        <div class="theme-info info-item">
            由 <a target="_blank" href="https://hexo.io">Hexo</a> 驱动&nbsp;|&nbsp;主题&nbsp;<a class="theme-version" target="_blank" href="https://github.com/EvanNotFound/hexo-theme-redefine">Redefine v0.4.6</a>
        </div>
        
        
        <script async data-pjax defer>
            function odometer_init(){
                    let el = document.getElementsByClassName('odometer');
                    for (i = 0; i < el.length; i++) {
                        od = new Odometer({
                            el: el[i],
                            format: '( ddd).dd',
                            duration: 200
                        });
                    }
            }
            odometer_init();
        </script>
        <div id="start_time_div" style="display:none">
            2023/1/4 12:00:00
        </div>
        
            <div>
                博客已运行 <span class="odometer" id="runtime_days" ></span> 天 <span class="odometer" id="runtime_hours"></span> 小时 <span class="odometer" id="runtime_minutes"></span> 分钟 <span class="odometer" id="runtime_seconds"></span> 秒
            </div>
        
        
        
    </div>  
</footer>
        </div>
    </div>

    
        <div class="post-tools">
            <div class="post-tools-container">
    <ul class="tools-list">
        <!-- TOC aside toggle -->
        
            <li class="tools-item page-aside-toggle">
                <i class="fa-regular fa-outdent"></i>
            </li>
        

        <!-- go comment -->
        
    </ul>
</div>

        </div>
    

    <div class="right-bottom-side-tools">
        <div class="side-tools-container">
    <ul class="side-tools-list">
        <li class="tools-item tool-font-adjust-plus flex-center">
            <i class="fa-solid fa-magnifying-glass-plus"></i>
        </li>

        <li class="tools-item tool-font-adjust-minus flex-center">
            <i class="fa-solid fa-magnifying-glass-minus"></i>
        </li>

        <li class="tools-item tool-expand-width flex-center">
            <i class="fa-solid fa-left-right"></i>
        </li>

        <li class="tools-item tool-dark-light-toggle flex-center">
            <i class="fa-solid fa-moon"></i>
        </li>

        <!-- rss -->
        

        
            <li class="tools-item tool-scroll-to-top flex-center">
                <i class="fa-solid fa-arrow-up"></i>
            </li>
        

        <li class="tools-item tool-scroll-to-bottom flex-center">
            <i class="fa-solid fa-arrow-down"></i>
        </li>
    </ul>

    <ul class="exposed-tools-list">
        <li class="tools-item tool-toggle-show flex-center">
            <i class="fa-solid fa-cog fa-spin"></i>
        </li>
        
    </ul>
</div>

    </div>

    <div class="image-viewer-container">
    <img src="">
</div>


    

</main>




<script src="/sakura-blog/js/utils.js"></script>

<script src="/sakura-blog/js/main.js"></script>

<script src="/sakura-blog/js/header-shrink.js"></script>

<script src="/sakura-blog/js/back2top.js"></script>

<script src="/sakura-blog/js/dark-light-toggle.js"></script>





    
<script src="/sakura-blog/js/code-copy.js"></script>




    
<script src="/sakura-blog/js/lazyload.js"></script>




    
<script src="/sakura-blog/js/runtime.js"></script>

    
<script src="/sakura-blog/js/odometer.min.js"></script>

    
<link rel="stylesheet" href="/sakura-blog/css/odometer-theme-minimal.css">



<div class="post-scripts pjax">
    
        
<script src="/sakura-blog/js/toc-toggle.js"></script>

<script src="/sakura-blog/js/libs/anime.min.js"></script>

<script src="/sakura-blog/js/toc.js"></script>

    
</div>


    
<script src="/sakura-blog/js/libs/pjax.min.js"></script>

<script>
    window.addEventListener('DOMContentLoaded', () => {
        window.pjax = new Pjax({
            selectors: [
                'head title',
                '.page-container',
                '.pjax'
            ],
            history: true,
            debug: false,
            cacheBust: false,
            timeout: 0,
            analytics: false,
            currentUrlFullReload: false,
            scrollRestoration: false,
            // scrollTo: true,
        });

        document.addEventListener('pjax:send', () => {
            REDEFINE.utils.pjaxProgressBarStart();
        });

        document.addEventListener('pjax:complete', () => {
            REDEFINE.utils.pjaxProgressBarEnd();
            window.pjax.executeScripts(document.querySelectorAll('script[data-pjax], .pjax script'));
            REDEFINE.refresh();
        });
    });
</script>



</body>
</html>
